package com.bwf.jdbc;

import java.sql.*;
import java.util.Date;

/**
 * @Version v1.0
 * @Description: TODO
 * @Author ZHANZHONGXIN
 * @Date 2022/6/1 15:24
 * @Copyright 博为峰
 */
public class StudentDao {
//    public Connection getConn() throws SQLException {
//        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?serverTimezone=UTC", "root", "123456");
//        return connection;
//    }

    public void update(Student student) {
        try (Connection connection = DBUtil.getConn();
             PreparedStatement preparedStatement = connection.prepareStatement("update t_student set salary = ? where name = ?");) {
            //设置数据
            preparedStatement.setInt(1, 100);
            preparedStatement.setString(2, student.getName());
            //执行
            preparedStatement.executeUpdate();
        } catch (Exception e) {

        }
    }

    public void insert(Student student) {
        PreparedStatement preparedStatement = null;
        Connection connection = null;
        try {
            connection = DBUtil.getConn();
            //SQL 语句被预编译并存储在 PreparedStatement 对象中
            String sql = "insert into `t_student`(name,sex,salary,birthday,create_time,update_time,class_id) values (?, ?, ?, ?, ?, ?, ?)";
            preparedStatement = connection.prepareStatement(sql);
            //设置数据
            preparedStatement.setString(1, student.getName());
            preparedStatement.setString(2, student.getSex());
            preparedStatement.setInt(3, student.getSalary());
            //java.util.Date
            Date birthday = student.getBirthday();
            //setDate需要java.sql.Date
            preparedStatement.setDate(4, new java.sql.Date(birthday.getTime()));
            Date createTime = student.getCreateTime();
            preparedStatement.setDate(5, new java.sql.Date(createTime.getTime()));
            preparedStatement.setDate(6, new java.sql.Date(student.getUpdateTime().getTime()));
            preparedStatement.setInt(7, student.getClassId());
            //执行
            int i = preparedStatement.executeUpdate();
            System.out.println("i = " + i);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    public void query(Student student) throws SQLException {
        //2.获取连接
        Connection connection = DBUtil.getConn();
        //3.执行SQL
        PreparedStatement preparedStatement = connection.prepareStatement("select * from t_student where name = ?");
        //4.设置数据
        preparedStatement.setString(1, student.getName());
        //4.结果集
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            String name = resultSet.getString("name");
            System.out.println(name);
            int salary = resultSet.getInt(5);
            System.out.println("salary = " + salary);
        }
    }
}
